<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of SwitchModel
 *
 * @author Admin
 */
class Application_Model_HouseModel extends Zend_Db_Table_Abstract {

    protected $_name = 'house_mgmt';
    protected $_fields = 'id,official_address,address,postadress,postnr,owner_name,customer_code,registration_date,seller,personnr,tel1,tel2,email,installation_avropad,blast_klar,install_klar,aviserad_vecka,installerad_av,cabinet,switch,status,street_digging_status,house_digging_status,installation_status,installation_user_id,house_digging_user_id,marketing_user_id,marketing_interaction1,marketing_interaction2,marketing_interaction3,marketing_interaction4';
    public $adapter;

    function init() {
        $this->adapter = $this->getAdapter();
    }

    function getAllHouses($request, $switchId = 0) {
        $select = $this->adapter->select()
                        ->from($this->_name, array('id', 'official_address', 'address', 'owner_name', 'customer_code', 'cabinet', 'switch', 'status','registration_date','seller','personnr','tel1','tel2','email','installation_avropad','blast_klar','install_klar','aviserad_vecka','installerad_av','postadress','postnr'))
                        ->joinLeft('cabinet_mgmt', 'cabinet_mgmt.id = cabinet', array('name as cabinet_name'))
                        ->joinLeft('switch_mgmt', 'switch_mgmt.id = switch', array('name as switch_name'));
        if ($switchId) {
            $select->where("switch = '$switchId'");
        }
        $dbSelectPaginator = new Zend_Paginator_Adapter_DbSelect($select);
        $paginator = new Zend_Paginator($dbSelectPaginator);
        $paginator->setCurrentPageNumber($request->getParam('page'));
        $paginator->setPageRange(5);
        $paginator->setItemCountPerPage(5);
        return $paginator;
    }

    function save($data) {
        if (isset($data['id'])) {
            return $this->update($data, "id = $data[id]");
        } else {
            return $this->insert($data);
        }
    }

    function getHouse($field, $value) {
        $query = "SELECT `house_mgmt`.`id`, `house_mgmt`.`official_address`, `house_mgmt`.`address`, `house_mgmt`.`owner_name`, `house_mgmt`.`customer_code`, `house_mgmt`.`cabinet`, `house_mgmt`.`switch`, `house_mgmt`.`status`, `cabinet_mgmt`.`name` AS `cabinet_name`, `switch_mgmt`.`name` AS `switch_name`,registration_date,seller,personnr,tel1,tel2,email,installation_avropad,blast_klar,install_klar,aviserad_vecka,installerad_av,postadress,postnr
                    FROM `house_mgmt` 
                    LEFT JOIN `cabinet_mgmt` ON cabinet_mgmt.id = cabinet 
                    LEFT JOIN `switch_mgmt` ON switch_mgmt.id = switch 
                    WHERE ($this->_name.$field = '$value')";

        return $this->adapter->fetchRow($query);
    }

    function getHouseRemarks($id, $page) {
        $select = $this->adapter->select()
                        ->from('house_remarks', array('id', 'house_id', 'remark', 'posted_at', 'user_id'))
                        ->WHERE("house_id = '$id'")
                        ->order("posted_at DESC");
        $dbSelectPaginator = new Zend_Paginator_Adapter_DbSelect($select);
        $paginator = new Zend_Paginator($dbSelectPaginator);
        $paginator->setCurrentPageNumber($page);
        $paginator->setPageRange(5);
        $paginator->setItemCountPerPage(2);
        return $paginator;
    }

    function saveRemarks($data) {
        return $this->adapter->insert('house_remarks', $data);
    }

    function getAllUserAssignedSwitcheHousesForMarketing($request, $userId) {
        $select = $this->adapter->select()
                        ->from($this->_name, array('id', 'official_address', 'address', 'status','registration_date','seller','personnr','tel1','tel2','email','installation_avropad','blast_klar','install_klar','aviserad_vecka','installerad_av','postadress','postnr'))
                        ->joinInner('switch_users', "switch_users.switch_id = house_mgmt.switch", array())
                        ->joinLeft('cabinet_mgmt', "cabinet_mgmt.id = house_mgmt.cabinet", array('name as cabinet_name', 'id as cabinet_id'))
                        ->joinLeft('switch_mgmt', "switch_mgmt.id = house_mgmt.switch", array('name as switch_name', 'id as switch_id'))
                        ->WHERE("switch_mgmt.marketing_status = 'Open' AND switch_mgmt.planning_percent_done = 100 AND switch_users.user_id='{$userId}' AND house_mgmt.marketing_user_id = '$userId'");
        $dbSelectPaginator = new Zend_Paginator_Adapter_DbSelect($select);
        $paginator = new Zend_Paginator($dbSelectPaginator);
        $paginator->setCurrentPageNumber($request->getParam('page'));
        $paginator->setPageRange(5);
        $paginator->setItemCountPerPage(5);
        return $paginator;
    }

    function getAllSwitchHouses($request, $switchId = 0) {
        $select = $this->adapter->select()
                ->from($this->_name, array('id', 'official_address', 'address', 'owner_name', 'customer_code', 'cabinet', 'switch', 'status', 'street_digging_status','house_digging_status','installation_status','registration_date','seller','personnr','tel1','tel2','email','installation_avropad','blast_klar','install_klar','aviserad_vecka','installerad_av','postadress','postnr'))
                ->joinLeft('switch_mgmt', 'switch_mgmt.id = switch', array('name as switch_name'))
                ->where("switch = '$switchId'");

        $dbSelectPaginator = new Zend_Paginator_Adapter_DbSelect($select);
        $paginator = new Zend_Paginator($dbSelectPaginator);
        $paginator->setCurrentPageNumber($request->getParam('page'));
        $paginator->setPageRange(5);
        $paginator->setItemCountPerPage(5);
        return $paginator;
    }

    function getHouseProgress($switchId) {
        return $this->adapter->fetchOne("select avg(street_digging_status) from house_mgmt where switch = '$switchId' group by switch");
    }
    
    function getHouseDiggingProgress($switchId){
        return $this->adapter->fetchOne("select avg(house_digging_status) from house_mgmt where switch = '$switchId' group by switch");
    }
    
    function getHouseDetail($id){
        return $this->adapter->fetchRow("select $this->_fields from $this->_name where id = '$id'");
    }

    function getAllHouseDetail($field, $id) {
        return $this->adapter->fetchAll("select $this->_fields from $this->_name where $field = '$id'");
    }

    function getHousesProgeressForEconomy($request, $switchId) {
        $select = $this->adapter->select()
                        ->from($this->_name, array('id as house_id', 'owner_name', 'street_digging_status','registration_date','seller','personnr','tel1','tel2','email','installation_avropad','blast_klar','install_klar','aviserad_vecka','installerad_av','postadress','postnr'))
                        ->WHERE("house_mgmt.switch = '$switchId'");
        $dbSelectPaginator = new Zend_Paginator_Adapter_DbSelect($select);
        $paginator = new Zend_Paginator($dbSelectPaginator);
        $paginator->setCurrentPageNumber($request->getParam('page'));
        $paginator->setPageRange(5);
        $paginator->setItemCountPerPage(5);
        return $paginator;
    }
    
    function getInstallationProgress($switchId){
        return $this->adapter->fetchOne("select avg(installation_status) from house_mgmt where switch = '$switchId' group by switch");
    }
    
}